/**
 * 
 */
package model.admin;

import java.sql.ResultSet;
import java.util.ArrayList;

import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;

import myutil.Connect;
import vo.CollegeResidentialVo;
import vo.ResidentialRoomVo;
import vo.StudentDetailsVo;
import vo.mixed.HostelStudentVo;

/**
 * @author Sony
 *
 */
public class HostelMo {
	ArrayList<ResidentialRoomVo> rooms=new ArrayList<ResidentialRoomVo>();
	ArrayList<CollegeResidentialVo> hostels=new ArrayList<CollegeResidentialVo>();
	ArrayList<HostelStudentVo> studentsOfHostelList = new ArrayList<HostelStudentVo>();
	String name="",roll="";
	ArrayList<Object> roll_id =new ArrayList<Object>();
	
	public void editHostel(CollegeResidentialVo vo, String string,String type) throws Exception {
		Connect c = new Connect();
		String query = "";
		if (string.equals("edit")) {
			query = "update college_residential set residential_name='"
					+ vo.getResidential_name() + "',residential_type='"
					+ vo.getResidential_type() + "',warden_id="
					+ vo.getWarden_id() + ",warden_name='"
					+ vo.getWarden_name() + "',capacity=" + vo.getCapacity()
					+ " where residential_id=" + vo.getResidential_id();
		} else if (string.equals("add")) {
			query = "insert into college_residential(residential_name,residential_type,warden_id,warden_name,capacity) values('"
					+ vo.getResidential_name()+ "','"+ vo.getResidential_type()+"',"+vo.getWarden_id()+",'"+vo.getWarden_name()+"',"+vo.getCapacity()+")";
		}
		System.out.println(query);
		try {
			c.dml(query);
			if(string.equals("edit")){
				c.logger(type, "Update");
			}else{
				c.logger(type, "Insert");
			}
		
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("edit hostel details failed");
		}

	}

	public void editRoom(ResidentialRoomVo vo, String string,String type) throws Exception {
		Connect c = new Connect();
		String query = "";
		if (string.equals("edit")) {
			query = "update residential_room set room_number="
					+ vo.getRoom_number() + ",vacancy=" + vo.getVacancy()
					+ ",occupancy=" + vo.getOccupancy() + ",description='"
					+ vo.getDescription() + "' where room_id="
					+ vo.getRoom_id();
		} else if (string.equals("add")) {
			query = "insert into residential_room(residential_id,room_number,vacancy,occupancy,description) values("
					+ vo.getResidential_id()
					+ ","
					+ vo.getRoom_number()
					+ ","
					+ vo.getVacancy()
					+ ","
					+ vo.getOccupancy()
					+ ",'"
					+ vo.getDescription() + "')";
			
		}
		System.out.println(query);
		try {
			c.dml(query);
			if (string.equals("edit")){
				c.logger(type, "Update");
			}else {
				c.logger(type, "Insert");
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("edit room details failed");
		}
	}

	public void deleteHostel(int residential_id,String type) throws Exception {
		Connect c = new Connect();
		String query = "delete from college_residential where residential_id="+residential_id;
		System.out.println(query);
		try {
			c.dml(query);
			c.logger(type, "Delete");
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("deleting hostel details failed");
		}
	}

	public void deleteRoom(int room_id,String type) throws Exception {
		Connect c = new Connect();
		String queryToGetHostelList= "select sd.student_id as student_id from residential_room rr,college_residential cr , student_details sd,student_batch sb where rr.room_id= " + room_id +" and rr.residential_id=cr.residential_id and sb.room_id=rr.room_id and sd.student_id=sb.student_id";		//String query = "delete from residential_room where room_id="+room_id;
		
		//System.out.println(query);
		try {
			ResultSet rs=c.getResult(queryToGetHostelList);
			while(rs.next())
			{
				roll_id.add(rs.getString("student_id"));
			}
			
			for(int i = 0 ; i < roll_id.size(); i++)
			{
				String room_delete_query  =  " Update student_batch set room_id = NULL where student_id = " + roll_id.get(i);
				
				//ResultSet rs1=c.getResult(room_delete_query);
				c.dml(room_delete_query);
				c.logger(type, "Update");
			}
	
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("deleting room failed");
		}
		
	}
	public ArrayList<CollegeResidentialVo> getHostelDetails(int residential_id) throws Exception {
		Connect c=new Connect();
		String queryToGetHostelList="select * from college_residential where residential_id="+residential_id;
		System.out.println(queryToGetHostelList);
		try {
			ResultSet rs=c.getResult(queryToGetHostelList);
			while(rs.next())
			{
				CollegeResidentialVo voObject=new CollegeResidentialVo();
				voObject.setResidential_name(rs.getString("residential_name"));
				voObject.setCapacity(rs.getInt("capacity"));
				voObject.setResidential_id(rs.getInt("residential_id"));
				voObject.setResidential_type(rs.getString("residential_type"));
				voObject.setWarden_id(rs.getInt("warden_id"));
				voObject.setWarden_name(rs.getString("warden_name"));
				hostels.add(voObject);	
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("getting hostel details failed");
		}
		
		return hostels;
	}

	public ArrayList<ResidentialRoomVo> getRoomDetails(int room_id) throws Exception {
		Connect c=new Connect();
		//int noOfStudents=0;
	/*	String Occupant_ID = "";
		
		String queryOccupant_ID=" select sd.roll_number as roll_number from residential_room rr,college_residential cr , student_details sd,student_batch sb where rr.room_id="+room_id+" and rr.residential_id=cr.residential_id and sb.room_id=rr.room_id and sd.student_id=sb.student_id";
		ResultSet rs1=c.getResult(queryOccupant_ID);
		if(rs1.next())
		{  
			Occupant_ID = "";
			rs1.previous();
			while(rs1.next())
			{
	
				Occupant_ID+=rs1.getString("roll_number")+"   ";
			}
		}
		else
		{
			Occupant_ID+= "--";
		}
*/
	/*	ResultSet rs1;
		String query1="select * from student_batch where room_id="+room_id;
		rs1=c.getResult(query1);
		while(rs1.next())
		{
			noOfStudents=noOfStudents+1;
		} */
		
		String queryToGetHostelList="select rr.room_id,rr.description,rr.occupancy,rr.residential_id,cr.residential_name,rr.room_number,rr.vacancy from residential_room rr,college_residential cr where rr.room_id="+room_id+" and rr.residential_id=cr.residential_id";
		System.out.println(queryToGetHostelList);
		try {
			ResultSet rs=c.getResult(queryToGetHostelList);
			while(rs.next())
			{
				ResidentialRoomVo voObject=new ResidentialRoomVo();
				voObject.setRoom_id(room_id);
				voObject.setDescription(rs.getString("description"));
				voObject.setOccupancy(rs.getInt("occupancy"));
				voObject.setResidential_id(rs.getInt("residential_id"));
				voObject.setRoom_number(rs.getInt("room_number"));
				voObject.setVacancy(rs.getInt("vacancy"));
				voObject.setResidential_name(rs.getString("residential_name"));
				//voObject.setOccupant_id(Occupant_ID);
				rooms.add(voObject);
				
			}			
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("getting room details failed");
		}
		
		return rooms;
	}
	
	public ArrayList<StudentDetailsVo> getOccupantId(int room){
		ArrayList<StudentDetailsVo> occupantlist= new ArrayList<StudentDetailsVo>();
		String query="";
		Connect c= new Connect();
		query="select sd.roll_number as roll_number from residential_room rr,college_residential cr , student_details sd,student_batch sb where rr.room_id="+room+" and rr.residential_id=cr.residential_id and sb.room_id=rr.room_id and sd.student_id=sb.student_id";
		System.out.println(query);
		try {
			ResultSet rs= c.getResult(query);
			while(rs.next()){
				StudentDetailsVo vo = new StudentDetailsVo();
				vo.setRoll_number(rs.getString("roll_number"));
				occupantlist.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return occupantlist;
	}
	
	public void deleteStudentFromRoom(String roll_number, int room_id,String type){
		String query="",query2="";
		Connect c= new Connect();
		query="update student_batch set room_id="+null+" where student_id=(select student_id from student_details where roll_number='"+roll_number+"' ) ";
		System.out.println(query);
		query2="update residential_room set vacancy=vacancy+"+1+" where room_id="+room_id;
		System.out.println(query2);
		try {
			c.dml(query);
			c.dml(query2);
			c.logger(type, "update");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public ArrayList<CollegeResidentialVo> hostelList() throws Exception {
		Connect c=new Connect();
		String queryToGetHostelList="select * from college_residential";
		try {
			ResultSet rs=c.getResult(queryToGetHostelList);
			while(rs.next())
			{
				CollegeResidentialVo voObject=new CollegeResidentialVo();
				voObject.setResidential_name(rs.getString("residential_name"));
				voObject.setCapacity(rs.getInt("capacity"));
				voObject.setResidential_id(rs.getInt("residential_id"));
				voObject.setResidential_type(rs.getString("residential_type"));
				voObject.setWarden_id(rs.getInt("warden_id"));
				voObject.setWarden_name(rs.getString("warden_name"));
				hostels.add(voObject);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("getting hostel failed");
		}
		
		return hostels;
	}


	public ArrayList<HostelStudentVo> getStudentsFromHostels(int residentialId) throws Exception {
		//System.out.println("residentialId : " + residentialId);
		String queryToGetStudentsInHostel = "select sd.student_id,sd.roll_number,sd.student_name,rr.room_id as room_id,room_number,residential_name,rr.description as description "
				+ "from student_details sd,student_batch sb,college_residential cr,residential_room rr "
				+ "where cr.residential_id=rr.residential_id "
				+ "and sb.room_id=rr.room_id "
				+ "and sd.student_id=sb.student_id and rr.residential_id="
				+ residentialId;
		System.out.println(queryToGetStudentsInHostel);
		Connect c = new Connect();
		try {
			ResultSet rs = c.getResult(queryToGetStudentsInHostel);
			if (rs.next()) {
				HostelStudentVo hostelStudentVoObject = new HostelStudentVo();
				hostelStudentVoObject.setRoom_id(rs.getInt("room_id"));
				hostelStudentVoObject.setRoom_number(rs.getInt("room_number"));
				hostelStudentVoObject.setResidential_id(residentialId);
				hostelStudentVoObject.setResidential_name(rs.getString("residential_name"));
				hostelStudentVoObject.setDescription(rs.getString("description"));
				rs.previous();
				while(rs.next())
				{
					name+=rs.getString("student_name")+"   ";
					roll+=rs.getString("roll_number")+"   ";
				}
				hostelStudentVoObject.setStudent_name(name);
				hostelStudentVoObject.setRoll_number(roll);

				studentsOfHostelList.add(hostelStudentVoObject);
			}
		} catch (Exception e) {
			//e.printStackTrace();
			throw new Exception("getting student list failed");
		}
		return studentsOfHostelList;

	}

	public ArrayList<HostelStudentVo> getAllStudentsFromHostels(
			int residential_id) throws Exception {
		
		//System.out.println("residentialId : " + residential_id);
		String queryToGetStudentsInHostel = "select rr.room_id as room_id,room_number,residential_name,rr.description as description "
				+ " from college_residential cr,residential_room rr "
				+ "where cr.residential_id=rr.residential_id "
				+ "and rr.residential_id="
				+ residential_id;
		System.out.println(queryToGetStudentsInHostel);
		Connect c = new Connect();
		try {
			ResultSet rs = c.getResult(queryToGetStudentsInHostel);
			while (rs.next()) {
				HostelStudentVo hostelStudentVoObject = new HostelStudentVo();
				hostelStudentVoObject.setRoom_id(rs.getInt("room_id"));
				hostelStudentVoObject.setRoom_number(rs.getInt("room_number"));
				hostelStudentVoObject.setResidential_name(rs.getString("residential_name"));
				hostelStudentVoObject.setDescription(rs.getString("description"));
				hostelStudentVoObject.setResidential_id(residential_id);
				
				String query1 = "select sd.student_id,sd.roll_number,sd.student_name,rr.room_id as room_id,room_number,residential_name,rr.description as description "
						+ "from student_details sd,student_batch sb,college_residential cr,residential_room rr "
						+ "where cr.residential_id=rr.residential_id "
						+ "and sb.room_id=rr.room_id "
						//+ "and sb.room_id="+rs.getInt("room_id")+" "
						+ "and rr.room_number="+rs.getInt("room_number")+" "
						+ "and sd.student_id=sb.student_id and rr.residential_id="
						+ residential_id;
				System.out.println(query1);
				ResultSet rs1 = c.getResult(query1);
				if(rs1.next())
				{
					name = "";
					roll = "";
					rs1.previous();
					while(rs1.next()){
						name+=rs1.getString("student_name")+"   ";
						roll+=rs1.getString("roll_number")+"   ";
					}
					hostelStudentVoObject.setStudent_name(name);
					hostelStudentVoObject.setRoll_number(roll);
				}
				else{
				hostelStudentVoObject.setStudent_name("--");
				hostelStudentVoObject.setStudent_id(0);
				hostelStudentVoObject.setRoll_number("--");
				}
			//	System.out.println(rs.getInt("room_id") + "  "+ rs.getInt("room_number") + "  "	+ rs.getString("residential_name") + "  "+ rs.getString("description"));
				
				studentsOfHostelList.add(hostelStudentVoObject);
			}
		} catch (Exception e) {
			//e.printStackTrace();
			throw new Exception("getting hostel/student details failed");
		}
		return studentsOfHostelList;
	}

public void updateRoomOccupancy(String roll_number,int room_id,int room_number,String desc,int vacancy,int occupancy,String type){
	String query1="",query2="",query3="";
	Connect c = new Connect();
	query1="update student_batch set room_id="+room_id+" where student_id=(select student_id from student_details where roll_number='"+roll_number+"')";
	System.out.println(query1);
	query2="update residential_room set room_number="+room_number+" , description='"+desc+"' ,vacancy="+vacancy+" ,occupancy="+occupancy+" where room_id="+room_id;
	System.out.println(query2);
	query3="update residential_room set vacancy=vacancy-"+1+" where room_id="+room_id;
	System.out.println(query3);
	try {
		c.dml(query1);
		c.dml(query2);
		c.dml(query3);
		c.logger(type, "Update");
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

}
